CODEWITHSUNDEEP
mysqlsqlunion
Sammy Davis Jr.
Sammy Davis Jr.

Reputation: 35

How to write this simple MySQL JOIN query?

I need to select records from 2 tables, one called cities and one called neighborhoods. They both share a table column in common called parent_state. In this cell the id of the parent state is stored.

I need to select all cities and neighborhoods that belong to a certain state. For example if the state id is 10, I need to get all the cities and neighborhoods that has this value for it's parent_state cell.

The state id is stored in a PHP variable like so:

$parent_state = '10';

What would this query look like (preferably the merged results from both tables should be sorted by the column name in alphabetical order)?

EDIT

Yes, I probably do need a union. I'm very new to mysql and all I can do at the moment is query tables individually.

I can always query both the cities and neighborhoods tables individually but the reason why I want to merge the results is for the sole purpose of listing said results alphabetically.

So can someone please show how the UNION query for this would look?

Upvotes: 1

Views: 3067

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332581

Use:

SELECT c.name
  FROM CITIES c
 WHERE c.parent_state = 10
UNION ALL
SELECT n.name
  FROM NEIGHBORHOODS h
 WHERE n.parent_state = 10

UNION ALL will return the result set as a combination of both queries as a single result set. UNION will remove duplicates, and is slower for it - this is why UNION ALL is a better choice, even if it's unlikely to have a city & neighbourhood with the same name. Honestly, doesn't sound like a good idea mixing the two, because a neighbourhood is part of a city...

Something else to be aware of with UNION is that there needs to be the same number of columns in the SELECT clause for all the queries being UNION'd (this goes for UNION and UNION ALL). IE: You'll get an error if the first query has three columns in the SELECT clause and the second query only had two.

Also, the data types have to match -- that means not returning a DATE/TIME data type in the same position was an other query returning an INTEGER.

Upvotes: 1

Pramendra Gupta
Pramendra Gupta

Reputation: 14873

select * from city as c
inner join neighborhoods as n
on n.parent_state = c.parent_state
where c.parent_state=10

You can use Left,Right Join, in case of city and nighborhoods dont have relational data.

Upvotes: 0

SingleNegationElimination
SingleNegationElimination

Reputation: 156158

What you want is probably not a join, but rather, a union. note that a union can only select the exact same columns from both of the joined expressions.

Upvotes: 1

Related Questions