CODEWITHSUNDEEP
phpmysqlsql
Hengjie
Hengjie

Reputation: 4672

How to return multiple rows by combining two tables

The following is a simplification of my problem, for the purposes of this question. I've two tables.

Table profile:

TABLE profile
profile_id  | first_name    | last_name
1           | David         | Battery
2           | John          | Motor

Table known_names:

TABLE known_names
profile_id  | first_name
1           | A
1           | B
1           | C
2           | X
2           | Y
2           | X

What I'm trying to achieve is to output known_names.first_name alongside profile.last_name when profile.profile_id = known_names.profile_id. BUT I also want to print out profile.first_name and profile.last_name in one of the rows in the result.

So the query may look like this (syntax is wrong, and I'm looking for the correct query to do this):

SELECT profile_id, first_name, last_name FROM profile CROSS JOIN known_names ON known_names.profile_id = profile.profile_id WHERE profile.first_name = 'David'

An example of the result is:

profile_id  | first_name    | last_name
1           | David         | Battery
1           | A             | Battery
1           | B             | Battery
1           | C             | Battery

How could I write a query that could achieve that?

Upvotes: 1

Views: 154

Answers (4)

fthiella
fthiella

Reputation: 49089

I have to admit that I don't use often a GROUP BY WITH ROLLUP, but I wanted to use it to answer your question without using a UNION query:

SELECT
  profile_id,
  COALESCE(first_name_k, first_name_p) First_Name,
  Last_Name
FROM (
  SELECT 
    p.profile_id, 
    k.first_name as first_name_k,
    p.first_name as first_name_p,
    p.last_name 
  FROM
    profile p inner join known_names k
    ON k.profile_id = p.profile_id
  WHERE
    p.first_name='David'
  GROUP BY
    p.profile_id,
    k.first_name WITH ROLLUP
  ) s
WHERE
  profile_id IS NOT NULL
ORDER BY
  first_name_k IS NOT NULL,
  COALESCE(first_name_k, first_name_p)

See it working here.

Upvotes: 1

khabraken
khabraken

Reputation: 166

Assuming you can use profile_id as an input to the query rather than first_name:

SELECT
  p.profile_id AS profileId,
  p.first_name AS firstName,
  p.last_name AS lastName
  FROM profile p
  WHERE p.profile_id = 1

UNION

SELECT
  k.profile_id AS profileId,
  k.first_name AS firstName,
  p.last_name AS lastName
  FROM known_names k
  INNER JOIN profile p ON p.profile_id = k.profile_id
  WHERE k.profile_id = 1

http://www.sqlfiddle.com/#!2/fa421/18

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

   Select t.profileId, t.firstName, t.lastName
    FROM
(
SELECT 
        p.profile_id as profileId, 
        p.first_name as firstName, 
        p.first_name as first_Name,
        p.last_name as lastName
        FROM profile p

    UNION 

        SELECT 
        p.profile_id as profileId, 
        k.first_name as firstName,
        p.first_name as first_Name,
        p.last_name as lastName 
        FROM profile p, 
        known_names k
        WHERE  k.profile_id = p.profile_id
) t
where t.first_Name='David'

Check the SQL-Fiddle

Upvotes: 2

John Woo
John Woo

Reputation: 263893

SELECT  a.*,
        b.first_name other_FirstName
FROM    profile a
        INNER JOIN known_names b
            ON a.profile_ID = b.profile_ID
// WHERE a.first_Name = ''
ORDER BY a.profile_id, a.first_Name


SELECT  a.*
FROM    profile a
WHERE   first_name = 'david'
UNION
SELECT  c.profile_id, c.first_name, d.last_name
FROM    profile b
        INNER JOIN known_names c
            ON b.profile_ID = c.profile_ID
        CROSS JOIN 
       (
          SELECT  last_Name
          FROM    profile 
          WHERE   first_name = 'david'
       ) d
WHERE   b.first_name = 'david'

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 0

Related Questions