Tricky Join Help!

[thedepotnetwork]

I am trying to do the following:

I have two tables, one contains all the vendors "vendors" the other contains the vendors service areas "vendors_service_areas"

 

I am attempting to only get the records from the "vendors_services_areas" table where the "vendor_state" in the "vendors" table is equal to 46 (utah). Both tables have the column, "vendor_id"

 

Here is what I had, but didn't work:

 

SELECT vca.vendor_id, vca.service_area, vca.service_type, vca.post_data

FROM vendors_service_areas AS vca, vendors AS v

WHERE v.vendor_state =46

AND v.vendor_id = vca.vendor_id

 

PLease Help! Still learning.

[Mchl]

It looks OK in general, although it could be rewritten as:

SELECT 
  vca.vendor_id, vca.service_area, vca.service_type, vca.post_data
FROM 
  vendors_service_areas AS vca 
CROSS JOIN 
  vendors AS v
USING(vendor_id)
WHERE 
  v.vendor_state =46 

 

But that's not really different.

 

What do you mean by 'it didn't work'. Did it throw any errors, or did it not return expected results?

 

 

[thedepotnetwork]

Thanks for the help.

 

What I mean is it didn't get all the results? I did a test, logged into our backend, and found that it missed many service areas... so something isn't right...

 

DIDN"T RETURN ALL EXPECTED RESULTS

[thedepotnetwork]

Hey! Yours worked! wow, thank you!

SELECT vca.vendor_id, vca.service_area, vca.service_type, vca.post_data
FROM vendors_service_areas AS vca
CROSS JOIN vendors AS v
USING ( vendor_id )
WHERE v.vendor_state =46 

 

Your SQL query has been executed successfully

[Mchl]

That's a bit strange, cause in general they're equivalent to each other.

[thedepotnetwork]

Yes, I thought it a bit strange... but thanks for helping me brush up on skills.

[thedepotnetwork]

One more for you... can't seem to get it:

similar thing, but in this case, different fields names...

SELECT *
FROM voting AS vt
CROSS JOIN vendors AS v
USING ( vt.item_id, v.vendor_id )
WHERE v.vendor_state =46 

[Mchl]

USING ( item_id, vendor_id )

 

And you can use USING only if the columns exist in both tables

[thedepotnetwork]

Yes, I tried but keep getting error:

 

SQL query: Documentation

SELECT *
FROM voting AS vt
CROSS JOIN vendors AS v
USING ( item_id, vendor_id )
WHERE v.vendor_state =46
LIMIT 0 , 30

MySQL said: Documentation
#1054 - Unknown column 'item_id' in 'from clause' 

 

item_id is on the table "voting" and it would be the matched "vendor_id" from vendors table where the state = 46

[Mchl]

You can't use USING in such a case

 

SELECT *
FROM voting AS vt
INNER JOIN vendors AS v
ON vt.item_id = v.vendor_id 
WHERE v.vendor_state =46

[thedepotnetwork]

I am new to this forum stuff, but wow, how does one repay you as a master programmer and help!?

[Mchl]

By becoming my henchman. This includes polishing my shoes and fetching me cold drinks.

You can also consider contributing into this thread:

http://www.phpfreaks.com/forums/index.php/topic,45685.0.html