Help writing a cross table query

[StevieM]

I have to write a query to return a field if the correct entries are in three tables.

eg. select ID from tableA where (tableA.1 = 'this' and tableB.2 = 'this' and tableC.3 = 'this')

Can someone point out how to do this?


- - - - - - - - - - - - -
There are 10 types of people in the world, those that understand binary and those that don't!!
- - - - - - - - - - - - -

 

[Welshbird]

'd suggest something like this, but I'm guessing on the joining fields:

select a.ID from tableA a, tableB b, table c
where
a.LINK = b.LINK
and
b.LINK = c.LINK
and a.1 = 'THISa'
and b.1 = 'THISb'
and c.1 = 'THISc'

Hope this helps - if you need more info then you need to post up a table structure for all three tables including info on which fields link across.



Fee

[red] Nadolig Llawen a Blwyddyn Newydd Dda [/red]

 

[StevieM]

Thanks for the reply but I cannot get this to work. Bear with me as I am new to SQL

I am not sure what you mean by the joining fields and links?

I need to return a list of tableA.ID where the 3 conditions are met, what are the joining fields?


- - - - - - - - - - - - -
There are 10 types of people in the world, those that understand binary and those that don't!!
- - - - - - - - - - - - -

 

[r937]

take your pick:

select TableA.ID 
  from TableA
inner
  join TableB
    on TableB.2 = TableA.1
inner
  join TableC
    on TableC.3 = TableB.2  
 where TableA.1 = 'this'

select ID 
  from (
       select TableA.ID
        where TableA.1 = 'this'
       union all 
       select TableB.ID
        where TableB.2 = 'this'
       union all 
       select TableC.ID
        where TableC.3 = 'this'
       ) as dt
group
    by ID       
having count(*) = 3

r937.com | rudy.ca

 

[Welshbird]

Ok, sorry! If you ahve three tables, then you must be looking for the three fields which euqal 'THIS' in specific records.

So, as an example, if I have a customer table, an Items table and an orders table, I'd have a field in the customer table with a customer ID.

In the Items table, i'd list all of the items with an Item ID.

In the orders table, I'd list the customer ID who ordered that item, the Item ID from the Items table of the thing they ordered, and then some other stuff about dates and costs and whatever.

So, I'd be able to link these like so:

select CustomerID from customers a, orders b, items c
where a.Customerid = b.customerID
and
b.ItemID = c.ItemID
where a.customername = 'Fee'
and
b.OrderDate = '2005-12-13'
and c.ItemID = '123456'

or something like that. I'm using an alias for each table (a,b,c) cos I just think it makes it easier to read and code (less typing!)

It might help to look on this site too:

http://www.techonthenet.com/top10/sql.php

Some good background to begin with SQL.

hope that helps more - let me know!



Fee

[red] Nadolig Llawen a Blwyddyn Newydd Dda [/red]

 

[StevieM]

I am trying this:

select a.accountID from Account a, Address b, Opportunity c
where a.type = 'Prospect'
and
b.postalcode like 'BG%'
and
c.status <> 'open'

This should return about 300 AccountID's but is returning none. Can you see an issue in the code? I tried taking it from one of the posts above but it still does not work.

Thanks


- - - - - - - - - - - - -
There are 10 types of people in the world, those that understand binary and those that don't!!
- - - - - - - - - - - - -

 

[Welshbird]

StevieM, I think the issue with your code is that SQL cannot 'know' which records in Address are related to the records you hope to find in Account, ditto with Opportunity.

I'm guessing that in both of these tables there must be a field relating to the Account.
I'm think ing you need something like this below, but maybe if you can post the structure of your tables (right click on the table in enterprise manager, and select 'generate SQL', then copy this on to the forum so we can see)

select a.accountID from Account a, Address b, Opportunity c
where a.type = 'Prospect'
and
b.postalcode like 'BG%'
and 
c.status <> 'open'
and a.accountid = b.account.id
and a.accountid = c.accountid

the
and a.accountid = b.account.id
and a.accountid = c.accountid
I can't tell you without knowing more about the tables.

Fee

[red] Nadolig Llawen a Blwyddyn Newydd Dda [/red]

 

[StevieM]

Ok, I see what you mean.

Now I have got my head round that bit I have managed to get it to work.

Thanks for all the help guys.


- - - - - - - - - - - - -
There are 10 types of people in the world, those that understand binary and those that don't!!
- - - - - - - - - - - - -