CODEWITHSUNDEEP
phpmysqlsql
Abhi
Abhi

Reputation: 5561

Getting similar rows from Database Table

Hello friends I am having following table structure of quote Table

enter image description here

I want to access all similar quotes (which have same author_id and category_id) for a particular _id(quoteId)

Earlier

Similar quotes means all columns of all those rows of this table of same category_id and same author_id. Two quotes can be considered as similar if their authors are same and category are same.

so I was using following query for this

 SELECT `related_quote`.* FROM
     `quote` AS `main_quote` LEFT JOIN 
    `quote` AS `related_quote`
     USING(`author_id`, `category_id`)
    WHERE `main_quote`.`_id` = QUOTE_ID

But now for some other requirement the definition of similar quotes changed slightly

Similar quotes means all columns of all those rows of this table of same category_id or same author_id. Two quotes can be considered as similar if their authors are same and category are same.

Please help me to create the query for OR condition thanks in adv

Upvotes: 2

Views: 93

Answers (2)

Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

You could use something like

SELECT
  t1.column1,
  t1.column2,
  t2.column1,
  t2.column2,
  t3.column1,
  t3.column2
FROM t1
  LEFT JOIN t2 ON (t2.ForeignKey = t1.primaryKey)
  LEFT JOIN t3 ON (t3.foreignKey = t2.PrimaryKey)
WHERE t1.columnName = 'somevalue';

Upvotes: 1

jimy
jimy

Reputation: 4908

SELECT related_quote.*
FROM quote AS main_quote
  LEFT JOIN quote AS related_quote
    ON (related_quote.author_id = main_quote.author_id OR
        related_quote.category_id = main_quote.category_id)
WHERE main_quote._id = main_quote._ID

Upvotes: 2

Related Questions