CODEWITHSUNDEEP
mysqlsql
Toleo
Toleo

Reputation: 774

Can't SELECT records from two tables properly

I've been using this Query at first, Then used this Query at last Which are

##Query[1]
SELECT t.title, p.content, p.version
FROM drafts d
INNER JOIN titles t ON t.id = d.tp_id AND d.t = 1
INNER JOIN posts p ON p.id = d.tp_id AND d.t = 2
WHERE user_id = 1

##Query[2]
SELECT coalesce(p.title, t.title), p.content, p.version
FROM drafts d
INNER JOIN titles t ON t.id = d.tp_id AND d.t = 1
INNER JOIN 
(
  SELECT pt.id, tp.title, pt.content, pt.version 
  FROM posts pt
  INNER JOIN titles tp ON tp.id = pt.tid
) p ON p.id = d.tp_id AND d.t = 2 
WHERE user_id = 1

What I want to do Using the Table: drafts to use

  1. Column: tp_id as an identifier to the Column: id in other tables.

  2. Column: user_id as the id of the user having the draft.

  3. Column: t as the identifier to which table of Tables: titles[t=1], posts[t=2] to fetch the records from it.

In Table: posts the Column: tid links to the Column: id of Table: titles Which is used to pull the Column: title.

I want no records to be shown from the Table: titles if doesn't exist, And no records to be shown from the Table: posts if the title related using tid on Table: titles doesn't exist Or the record of the Table: posts doesn't exist, That why I used INNER JOIN.

But in both queries I don't get any results at all.

Is this even the proper table scheme and design to be used for something like the Drafts table for two different tables instead of one for each table?

The result I expect is something like this

title       content     version
TheTitle    null        null
TheTitle    Content1    1
TheTitle    Content2    2
TheTitle    Content3    3
TheTitle    Content4    4
TitleThe    Content1    1
TitleThe    Content2    2
TitleThe    Content3    3
TitleThe    Content4    4

A simple Example:

    [table-a]                                        [table-b]
[id   -   title  ]                          [id   -   table_a_id   -   content  ]
[1    -   title-1]                          [1    -   1            -   content-1]
  1. table-a has the titles.
  2. table-b has the content of each title.
  3. table-b column of table_a_id links the content with the title of table-a.
                                    [table-ab]
                    [user_id   -   table_col_id   -   table_letter]
                    [1         -   1              -   a           ]
                    [1         -   1              -   b           ]
  1. table-ab has user_id to tell which user will be shown the records.
  2. table-ab has table_letter to tell the query which table to fetch the data from it.

What I want the Query to do is to check table-ab and fetch the data from the other tables according to it, so it would be

  1. table_letter = a => fetching rows from table-a.
  2. table_col_id = 1 => fetching rows from table-a WHERE id = 1.

result = title-1

  1. table_letter = b => fetching rows from table-b.
  2. table_col_id = 1 => fetching rows from table-b WHERE id = 1.

result = title-1 - content-1

Final result:
id -  title    -  content
-------------------------
1  -  title-1  -  null
2  -  title-1  -  content-1

Upvotes: 0

Views: 76

Answers (4)

kc2018
kc2018

Reputation: 1460

Do two separate queries (for t=1 and t=2) and use UNION ALL to combine the results:

SELECT t.title, null content , null version
FROM drafts d
INNER JOIN titles t
ON t.id = d.tp_id 
WHERE d.user_id = 1 and d.t = 1
UNION ALL
SELECT pt.title, pt.content, pt.version
FROM drafts d
INNER JOIN 
( SELECT p.id, t.title, p.content, p.version 
  FROM posts p
  INNER JOIN titles t ON t.id = p.tid ) pt
ON pt.id = d.tp_id AND d.t = 2
WHERE d.user_id = 1

Upvotes: 1

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

This will produce almost exactly what you have in your question. It does omit the line with the null values for the content and version. Not sure why that would have been in the expected output.

SELECT 
    if(d.t = 1,tp.title, if(d.t = 2,t.title,"")) as `title`,
    p.content,
    p.version
FROM drafts d
LEFT JOIN titles t ON t.id = d.tp_id AND d.t = 2
LEFT JOIN posts p ON t.id = p.tid
LEFT JOIN titles tp ON tp.id = p.tid AND d.t = 1
WHERE user_id = 1 AND NOT p.content IS NULL

I changed some of the data, specifically the groups of Content1 - Content4. I added "a" to the first 4, and "b" to the remainder.

Here's a SQL Fiddle that shows what it does.

Upvotes: 0

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

After joining with condition d.t = 1 there is no data for d.t = 2 That's why after 2nd Join you are not getting any data. Try the below Query

SELECT coalesce(p.title, t.title), p.content, p.version
FROM drafts d
INNER JOIN titles t 
ON t.id = d.tp_id 
INNER JOIN 
(
  SELECT pt.id, tp.title, pt.content, pt.version 
  FROM posts pt
  INNER JOIN titles tp 
  ON tp.id = pt.tid
) p 
ON p.id = d.tp_id 
WHERE user_id = 1
AND (d.t = 1 OR d.t = 2)

Upvotes: 0

Sukriti Singh
Sukriti Singh

Reputation: 53

As you are using inner join so please check the following:

  1. Common data in both tables(joining column), if there will not be any common value it will not pick any thing.
  2. Check data type of the column which are used to join both table that should be same if not then handle it
  3. Extra spaces in the joining column.

Upvotes: 0

Related Questions